﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Test.DAL
{
    public class Helper
    {
        //创建链接字符串
        public static string sqlConn = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        public static DataTable Query(string sql, params SqlParameter[] sqlParameters)
        {
            DataTable table = new DataTable();
            //创建数据库链接
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            //创建sqldataadapter对象
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql,sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
            }
            sqlDataAdapter.Fill(table);
            return table;
        }

        public static SqlDataReader Reader(string sql, params SqlParameter[] sqlParameters)
        {
            //创建链接对象
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            //打开数据库链接
            sqlConnection.Open();
            //创建sqlcommand对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlCommand.Parameters.AddRange(sqlParameters);
            }
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return sqlDataReader;
        }

        public static int NonQuery(string sql, params SqlParameter[] sqlParameters)
        {
            int num = 0;
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            try
            {
                sqlConnection.Open();
                if (sqlParameters.Length > 0)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters);
                }
                num = sqlCommand.ExecuteNonQuery();
            }
            finally {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                }
            }
            return num;
        }

    }
}
